This is a tutorial on how to set up an outbound csv file integration. We'll be sending an existing query / index query to a FTP site or HTTP end point.

Pre-requisites

To get started, you'll need to install the KIntegrations app in the account. Navigate to Website Manager > Market Place, search for KIntegrations and install the app.

Then create a website specifically for integrations if you don't have one already. Navigate to Website Manager > Websites and click Create new website. Ensure the website has KIntegrations enabled.

Creating the Pipeline

Navigate to Data > Integration. Click on the Endpoints tab and make sure the website you want to use for the integration job is selected in the top right corner. Click on Create Config or Edit Config which should open an XML text editor.

To create an pipeline, you'll need to enter some code. We will be creating two pipelines, one that will create the CSV file and another to export the file.

<integration>

  <endpoint 
    id="csv-create" 
    type="http" 
    address="127.0.0.0"
    pipelinePath="/integration/CreateCSV.xml"
    direction="out" 
    enabled="true" 
  />
  
  <endpoint 
    id="csv-export" 
    type="http" 
    address="127.0.0.0"
    pipelinePath="/integration/CreateCSV.xml"
    direction="out" 
    enabled="true" 
  />

</integration>

Once you've set up your pipeline, hit save and then exit back to the endpoints screen. You should see your pipeline listed. If you do not have a pipeline XML already created then click on the Create button under the Pipeline Path column.

This will create an XML that will dictate what the pipeline will actually do. For each endpoint, we will insert the below code which will run a JS function. For the first endpoint, we'll want to run the writeCsv function, for the second endpoint, we'll want to run the exportCsv function.

<TransactionStep alwaysRollback="false" isolated="false">

    <next class="JsRowStep">
        <jsPath>/integration/csvExporter.js</jsPath>
        <execFn>writeCsv</execFn> OR <execFn>exportCsv</execFn>
    </next>

</TransactionStep>

Next, we will want to create the JS file that we pointed to in the jsPath above. In this example, create a folder called integration if it doesn't already exist, then create a JS file with the name csvExporter as the path. 

For the writeCsv function, if you're exporting a table query, you can simply replace the table query name. If you're exporting an index query, then send the index query name to the parameter

function writeCsv() {
    var qm = services.queryManager;
    var fsm = services.fileStorageManager;
    var start = qm.commonStartDate;
    var end = qm.commonFinishDate;
    var orgIds = formatter.newArrayList();
    var params = formatter.newMap();

    //start a table query export job
    //var downloadJobId = qm.enqueueQueryTableExport("[name of table query]", start, end, orgIds, params);

    //start an index query export job
    //params.put("indexQueryName", "[name of index query]")
    //var downloadJobId = qm.enqueueQueryTableExport("tableIndexQuery", start, end, null, params);

    fsm.storeContent('integrations', 'csvExportJobId', fsm.toInputStream(formatter.toString(downloadJobId)));
}

function exportCsv() {
    var fsm = services.fileStorageManager;
    fsm.readContent('integrations', 'csvExportJobId', function (info) {
        downloadJobId = fsm.streamToString(info.data);
    });

    if (formatter.isNull(downloadJobId)) {
        views.throwServerException("downloadJobId couldnt be found");
    }

    var job = services.asyncJobManager.job(formatter.toLong(downloadJobId));

    if (formatter.isNull(job)) {
        views.throwServerException("Job couldnt be found: " + downloadJobId);
    }

    var output = job.jobOutput;
    var arr = formatter.fromCsv(output);
    var exportFile = arr[0];

    var ftpManager = services.ftpManager;
    var serverUrl = "www.kademi.co";
    var serverUserId = "user";
    var serverPassword = "password";
    var serverSecurity = "ssl";
    try {
        var client = ftpManager.openFtp(serverUrl, serverUserId, serverPassword, serverSecurity, true);
        var inputStream = null;

        fsm.readContent("reports", exportFile, function (info) {
            inputStream = info.data;
        });
        if (formatter.isNotEmpty(inputStream)) {
            client.upload("csv_file.csv", inputStream)
        }
    } catch (e) {
        log.error("FTP error")
    }

}

}